﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using ConfigLibrary;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Runtime.Serialization.Formatters.Binary;

namespace rfiInbox
{
    class mysqlconn
    {
        MySqlConnection conn = new MySqlConnection();
        MySqlCommand command = new MySqlCommand();
        public string connection;
        private Config config;
        private string _username;
        private string _host;
        private string _pass;
        private string _database;
        private int _port;

        public mysqlconn()
        {
            Stream s;
        #if (DEBUG)
            {
                s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);
            }

        #elif! (DEBUG)            
            {
                s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);
            }
        #endif

            BinaryFormatter b = new BinaryFormatter();
            config = (Config)b.Deserialize(s);
            s.Close();
            config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
        }

        public void connect(string type)
        {
            string[] database = _database.Split(new Char[] { ' ', ',' });
            if (type == "email") connection = "Database=" + database[0] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
            else if (type == "stores") connection = "Database=" + database[1] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
            conn.ConnectionString = connection;
            conn.Open();
            GC.KeepAlive(conn);
        }

        public DataRow loadstores(string store, string sequence)
        {
            connect("stores");
            command = new MySqlCommand("SELECT city, st, prj_typ FROM trident WHERE str_num = '" + store + "' AND str_seq = '" + sequence + "'");            
            DataRow row = MySqlHelper.ExecuteDataRow(connection, command.CommandText);
            conn.Close();
            return row;
        }

        public DataRow loadstores(string store)
        {
            connect("stores");
            command = new MySqlCommand("SELECT city, st, prj_typ FROM trident WHERE str_num = '" + store + "'");
            DataRow row = MySqlHelper.ExecuteDataRow(connection, command.CommandText);
            conn.Close();
            return row;
        }

        public DataRow loademail()
        {
            connect("email");
            command = new MySqlCommand("SELECT * FROM overdue ORDER by date_time desc limit 1");
            DataRow sentrow = MySqlHelper.ExecuteDataRow(connection, command.CommandText);            
            return sentrow;
        }
    }
}
